package com.irssproject.app.dao.recomanacions.impl;

import com.irssproject.app.dao.recomanacions.iface.RecommendationsDAO;
import com.irssproject.app.entitats.*;
import com.irssproject.app.entitats.recommendations.*;
import com.irssproject.app.utils.Constants;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.exception.ConstraintViolationException;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

/**
 *
 * @author adria
 */
public class RecommendationsDAOHibernate extends HibernateDaoSupport implements RecommendationsDAO {

    static final Logger loggerLocal = Logger.getLogger(RecommendationsDAOHibernate.class.getName());

    @Override
    public void insertReadingProfile(ReadingProfile perfil) {
        getHibernateTemplate().save(perfil);
    }

    @Override
    public ReadingProfile getReadingProfile(Long idusuari, Long idfeed, Long iditem, Long idconcepte) {
        try {
            String query = "From ReadingProfile pl "
                    + "where pl.user.id = ? "
                    + "and pl.feed.id = ? "
                    + "and pl.item.id = ? "
                    + "and pl.topic.id = ?";

            List<ReadingProfile> aux = (List<ReadingProfile>) getHibernateTemplate().find(query, new Object[]{idusuari, idfeed, iditem, idconcepte});

            return aux != null && aux.size() == 1 ? aux.get(0) : null;

        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage());
            return null;
        }
    }

    /**
     * retorna el id del concepte si no existeix
     *
     * @param tipus
     * @param valor
     * @return
     * @throws DataAccessException
     */
    @Override
    public Long insertTopic(String tipus, String valor) {
        Long idConcepte;
        try {
            idConcepte = (Long) getHibernateTemplate().save(new Topic(valor));
        } catch (DataIntegrityViolationException e) {
            //El concepte ja existeix, l'obtenim
            loggerLocal.log(Level.WARNING, "Concept {0} already exists and it will not be added", valor);
            idConcepte = (Long) getHibernateTemplate().find("select id from Topic where valor = ?", valor).get(0);
        }

        return idConcepte;
    }

    @Override
    public void esborrarClusters() {
        try {
            getHibernateTemplate().bulkUpdate("delete from ReadingProfile");
            getHibernateTemplate().bulkUpdate("delete from Topic");
        } catch (Exception ex) {
            loggerLocal.log(Level.SEVERE, "Error deleting clusters");
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<User> getNeighbors(User user) throws Exception {

        changeMaxResults(5);
        //users having less clusters frq than this threeshold won't be showed
        long thFreq = 8;
        //users having less topics than this threeshold won't be showed
        //first we get users than share some concepts with given user. Results will be ordered by number of concepts shared desc
        String query = "select uc2, sum(uc1.sumFreq + uc2.sumFreq) "
                + "from UserClustersRecommendation uc1, UserClustersRecommendation uc2 "
                + "where uc1.login <> uc2.login "
                + "and uc1.valueConcept = uc2.valueConcept "
                + "and uc1.idUser = ? "
                + "group by uc2.idUser, uc2.login "
                + "order by sum(uc1.sumFreq + uc2.sumFreq) desc";

        logger.info("Neighbors Query: " + query);

        List<Object> lst = (List<Object>) getHibernateTemplate().find(query, new Object[]{user.getId()});

        List<User> usrLst = new ArrayList<User>();

        for (Object obj : lst) {
            Object[] usrAndFrq = (Object[]) obj;
            UserClustersRecommendation uc = (UserClustersRecommendation) usrAndFrq[0];
            long frq = (Long) usrAndFrq[1];
            if (frq > thFreq) {
                usrLst.add((User) getHibernateTemplate().get(User.class, uc.getIdUser()));
            }

        }

        restoreMaxResults();
        return usrLst;
    }

    @Override
    public List<UserClustersRecommendation> getMatchingClusters(User connectedUser, User recommendedUser) {

        if (recommendedUser != null) {

            changeMaxResults(Constants.MAX_MATCHING_CLUSTERS);

            String qryThMax = "select max(uc2.sumFreq)/3 "
                    + "from UserClustersRecommendation uc1, UserClustersRecommendation uc2 "
                    + "where uc1.login <> uc2.login "
                    + "and uc1.valueConcept = uc2.valueConcept "
                    + "and uc1.idUser = ? "
                    + "and uc2.idUser = ? "
                    + "order by (uc1.sumFreq + uc2.sumFreq) desc";

            //Establishing a threeshold to discard less valued clusters
            Integer th = ((List<Integer>) getHibernateTemplate().find(qryThMax, new Object[]{connectedUser.getId(), recommendedUser.getId()})).get(0);

            String query = "select uc2 "
                    + "from UserClustersRecommendation uc1, UserClustersRecommendation uc2 "
                    + "where uc1.login <> uc2.login "
                    + "and uc1.valueConcept = uc2.valueConcept "
                    + "and uc1.idUser = ? "
                    + "and uc2.idUser = ? "
                    + "and uc2.sumFreq > ? "
                    + "order by (uc1.sumFreq + uc2.sumFreq) desc";

            logger.info("Matching clusters query: " + query);

            List<UserClustersRecommendation> topicsLst = (List<UserClustersRecommendation>) getHibernateTemplate().find(query, new Object[]{connectedUser.getId(), recommendedUser.getId(), th});

            restoreMaxResults();

            return topicsLst;
        } else {
            return new ArrayList<UserClustersRecommendation>();
        }
    }

    @Override
    public List<User> fillNeighbors(User user, String[] recommendedUsers) throws Exception {

        changeMaxResults(Constants.MAX_NEIGHBORS);
        //users having less clusters frq than this threeshold won't be showed
        long thFreq = 8;

        String qryNotIn;

        if (recommendedUsers.length > 0) {
            qryNotIn = " not in ( ";
            for (int i = 0; i < recommendedUsers.length - 1; i++) {
                qryNotIn += recommendedUsers[i] + ",";
            }

            qryNotIn += recommendedUsers[recommendedUsers.length - 1] + ") ";

        } else {
            qryNotIn = " = uc2.idUser ";
        }

        //users having less topics than this threeshold won't be showed
        //first we get users than share some concepts with given user. Results will be ordered by number of concepts shared desc
        String query = "select uc2, sum(uc1.sumFreq + uc2.sumFreq) "
                + "from UserClustersRecommendation uc1, UserClustersRecommendation uc2 "
                + "where uc1.login <> uc2.login "
                + "and uc1.valueConcept = uc2.valueConcept "
                + "and uc1.idUser = ? "
                + "and uc2.idUser " + qryNotIn + ""
                + "group by uc2.idUser, uc2.login "
                + "order by sum(uc1.sumFreq + uc2.sumFreq) desc";

        logger.info("Neighbors Query: " + query);

        List<Object> lst = (List<Object>) getHibernateTemplate().find(query, new Object[]{user.getId()});

        List<User> usrLst = new ArrayList<User>();

        for (Object obj : lst) {
            Object[] usrAndFrq = (Object[]) obj;
            UserClustersRecommendation uc = (UserClustersRecommendation) usrAndFrq[0];
            long frq = (Long) usrAndFrq[1];
            if (frq > thFreq) {
                usrLst.add((User) getHibernateTemplate().get(User.class, uc.getIdUser()));
            }

        }

        restoreMaxResults();
        return usrLst;
    }

    @Override
    public List<UserClustersRecommendation> fillMatchingClusters(User connectedUser, User recommendedUser, String[] recommendedTopics) {

        String qryNotIn;

        if (recommendedTopics.length > 0) {
            qryNotIn = " not in ( ";
            for (int i = 0; i < recommendedTopics.length - 1; i++) {
                qryNotIn += recommendedTopics[i] + ",";
            }

            qryNotIn += recommendedTopics[recommendedTopics.length - 1] + ") ";

        } else {
            qryNotIn = " = uc2.idConcept ";
        }
        changeMaxResults(Constants.MAX_MATCHING_CLUSTERS);

        String query = "select uc2 "
                + "from UserClustersRecommendation uc1, UserClustersRecommendation uc2 "
                + "where uc1.login <> uc2.login "
                + "and uc1.valueConcept = uc2.valueConcept "
                + "and uc1.idUser = ? "
                + "and uc2.idUser = ? "
                + "and uc2.idConcept " + qryNotIn + ""
                + "order by (uc1.sumFreq + uc2.sumFreq) desc";

        logger.info("Matching clusters query: " + query);

        List<UserClustersRecommendation> topicsLst = (List<UserClustersRecommendation>) getHibernateTemplate().find(query, new Object[]{connectedUser.getId(), recommendedUser.getId()});

        restoreMaxResults();

        return topicsLst;

    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Topic> getUserTopicsByLabelId(Long idusuari, Long idetiqueta) {
        return (List<Topic>) getHibernateTemplate().find("select co From ReadingProfile pl join pl.topic co where pl.user.id = ? and pl.label.id = ?", new Object[]{idusuari, idetiqueta});

    }

    @Override
    public List<Topic> getUserTopicsByFeedId(Long idusuari, Long idfeed) {
        return null;
        // TODO Auto-generated method stub

    }

    @Override
    @SuppressWarnings("unchecked")
    public List<UserClustersRecommendation> getTopClusters(User user) {

        changeMaxResults(Constants.MAX_TOP_FEEDS);
        List<UserClustersRecommendation> clustLst;
        if (user != null) {
            //select c.valor, count(*) as num_ocurrencies from conceptes c join perfils_lectura pl on  c.id = pl.idconcepte group by c.valor order by num_ocurrencies desc;
            clustLst = (List<UserClustersRecommendation>) getHibernateTemplate().find("from UserClustersRecommendation where idUser!=? order by sumFreq desc", new Object[]{user.getId()});
        } else {
            clustLst = (List<UserClustersRecommendation>) getHibernateTemplate().find("from UserClustersRecommendation order by sumFreq desc");
        }
        restoreMaxResults();
        return clustLst;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Label> getMostPopularLabels() {
        //select e.valor, count(*) as num_ocurrencies from etiquetes e join subscripcions s on e.idetiqueta = s.idetiqueta group by e.valor order by num_ocurrencies desc;
        return (List<Label>) getHibernateTemplate().find("select e from Subscription s join s.label as e group by e.id order by count(e) desc");
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Feed> getTopFeeds(User user) {
        changeMaxResults(Constants.MAX_TOP_FEEDS);
        List<Feed> feedsLst = (List<Feed>) getHibernateTemplate().find("select f from Reading l join l.subscription s join l.item as i join s.feed as f where s.user!=? group by f.id order by sum(l.relevance) desc", new Object[]{user});
        restoreMaxResults();
        return feedsLst;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Item> getTopItems(User user) {

        changeMaxResults(Constants.MAX_ITEMS);
        List<Item> itemsLst = (List<Item>) getHibernateTemplate().find("select i from Reading l join l.item i join fetch i.feed f join l.subscription s "
                + "where s.user!=? group by l order by sum(l.relevance) desc", new Object[]{user});
        restoreMaxResults();

        return itemsLst;
    }

    @Override
    public List<Item> fillTopItems(User user, String[] items) {
        changeMaxResults(Constants.MAX_ITEMS_FILL);

        List<Item> itemsLst = null;
        String notInClause = "", begin, end;
        if (items.length > 0) {
            begin = " and i.id not in (" + items[0];
            end = ")";
            for (int i = 1; i < items.length; i++) {
                notInClause += "," + items[i];
            }
            notInClause = begin + notInClause + end;
        } //else { notinclause will be "" and do not apply }


        try {
            itemsLst = (List<Item>) getHibernateTemplate().find("select i from Reading l join l.item i join fetch i.feed f join l.subscription s "
                    + "where s.user!=? " + notInClause + " group by l order by sum(l.relevance) desc", new Object[]{user});
        } catch (Exception e) {
            loggerLocal.log(Level.INFO, e.getMessage());
        }
        restoreMaxResults();
        return itemsLst;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Item> getTopItemsByCluster(User user, Long conceptId) {
        List<Item> itemsLst = null;
        changeMaxResults(Constants.MAX_ITEMS);

        try {
            itemsLst = (List<Item>) getHibernateTemplate().find("Select distinct i from ReadingProfile pl join pl.item i "
                    + "where pl.topic.id = ? and pl.user.id != ? "
                    + "order by i.publishedDate desc", new Object[]{conceptId, user.getId()});
        } catch (Exception e) {
            loggerLocal.log(Level.INFO, e.getMessage());
        }
        restoreMaxResults();

        return itemsLst;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Item> fillTopItemsByCluster(User user, Long conceptId, String[] items) {
        changeMaxResults(Constants.MAX_ITEMS_FILL);

        List<Item> itemsLst = null;
        String notInClause = "", begin, end;
        if (items.length > 0) {
            begin = " and i.id not in (" + items[0];
            end = ") ";
            for (int i = 1; i < items.length; i++) {
                notInClause += "," + items[i];
            }
            notInClause = begin + notInClause + end;
        } //else { notinclause will be "" and do not apply }


        try {
            Topic con = (Topic) getHibernateTemplate().get(Topic.class, conceptId);
            itemsLst = (List<Item>) getHibernateTemplate().find("Select i from ReadingProfile pl join pl.item i "
                    + "where pl.topic.id = ? and pl.user.id != ? "
                    + notInClause
                    + "order by i.publishedDate desc", new Object[]{conceptId, user.getId()});
        } catch (Exception e) {
            loggerLocal.log(Level.INFO, e.getMessage());
        }
        restoreMaxResults();
        return itemsLst;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Item> getTopItemsByFeed(User user, Long feedId) {

        changeMaxResults(Constants.MAX_ITEMS);
        List<Item> itemsLst = (List<Item>) getHibernateTemplate().find("select i from Reading l join l.item i join fetch i.feed f join l.subscription s where f.id = ? and s.user!=? group by l order by sum(l.relevance) desc", new Object[]{feedId, user});
        restoreMaxResults();
        return itemsLst;
    }

    @Override
    public List<Item> fillTopItemsByFeed(User user, Long feedId, String[] items) {
        changeMaxResults(Constants.MAX_ITEMS_FILL);

        List<Item> itemsLst = null;
        String notInClause = "", begin, end;
        if (items.length > 0) {
            begin = " and i.id not in (" + items[0];
            end = ")";
            for (int i = 1; i < items.length; i++) {
                notInClause += "," + items[i];
            }
            notInClause = begin + notInClause + end;
        } //else { notinclause will be "" and do not apply }


        try {
            itemsLst = (List<Item>) getHibernateTemplate().find("Select i from Item i, ReadingProfile pl join pl.feed f where i.feed = f and pl.feed.id = ? and pl.usuari!=? " + notInClause + " order by i.dataPublicacio desc", new Object[]{feedId, user});
        } catch (Exception e) {
            loggerLocal.log(Level.INFO, e.getMessage());
        }
        restoreMaxResults();
        return itemsLst;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Feed> getMostPopularFeedsByLabel(String idEtiqueta) {

        return (List<Feed>) getHibernateTemplate().find("select f from Reading l join l.subscripcio s join l.item as i join s.feed as f join s.label as e where e.id = ? group by f.id order by sum(l.relevance) desc", new Object[]{Integer.parseInt(idEtiqueta)});
    }

    @Override
    @SuppressWarnings("unchecked")
    public Topic getConcepteByTipusValor(String tipusConcepte, String valorConcepte) {

        List<Topic> con = (List<Topic>) getHibernateTemplate().findByExample(new Topic(valorConcepte));
        if (con.size() == 1) {
            return con.get(0);
        } else {
            return null;
        }

    }

    @Override
    public Topic getConcepteById(Long idConcepte) {
        return (Topic) getHibernateTemplate().get(Topic.class, idConcepte);
    }

    @Override
    public void incrementarFrequenciaConcepte(ReadingProfile perfil) {

        ReadingProfile pl = (ReadingProfile) getHibernateTemplate().get(ReadingProfile.class, perfil.getId());
        pl.setRelevance(pl.getRelevance() + 1);
        getHibernateTemplate().update(pl);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Topic> getConceptesUsuari(User usuari) throws Exception {
        return (List<Topic>) getHibernateTemplate().find("select c from ReadingProfile pl join pl.topic c where pl.user = ?", usuari);
    }

    @Override
    public List<Feed> getFeedsHavingConcepte(Topic concepte) {
        List<Feed> liFeeds = (List<Feed>) getHibernateTemplate().find("select f from ReadingProfile pl join pl.topic c join pl.feed f where c = ?", concepte);
        return liFeeds;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Item> getPersItemsByCluster(Long conceptId, Long userId) {

        changeMaxResults(Constants.MAX_ITEMS);

        List<Item> itemsLst = null;

        try {
            itemsLst = (List<Item>) getHibernateTemplate().find("Select i from ReadingProfile pl join pl.item i "
                    + "where pl.topic.id = ? and pl.user.id = ? "
                    + "order by i.publishedDate desc", new Object[]{conceptId, userId});
        } catch (Exception e) {
            loggerLocal.log(Level.INFO, e.getMessage());
        }
        restoreMaxResults();
        return itemsLst;

    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Item> fillPersItemsByCluster(Long conceptId, Long userId, String[] items) {
        changeMaxResults(Constants.MAX_ITEMS_FILL);

        List<Item> itemsLst = null;
        String notInClause = "", begin = "", end = "";

        if (items.length > 0) {
            begin = " and i.id not in (" + items[0];
            end = ")";
            for (int i = 1; i < items.length; i++) {
                notInClause += "," + items[i];
            }
            notInClause = begin + notInClause + end;
        } //else { notinclause will be "" and do not apply }

        try {
            itemsLst = (List<Item>) getHibernateTemplate().find("Select i from ReadingProfile pl join pl.item i "
                    + "where pl.topic.id = ? and pl.user.id = ? "
                    + notInClause
                    + "order by i.publishedDate desc", new Object[]{conceptId, userId});
        } catch (Exception e) {
            loggerLocal.log(Level.INFO, e.getMessage());
        }
        restoreMaxResults();
        return itemsLst;
    }

    @Override
    public void createUserSubsription(User user, User userToSubs) throws Exception {
        try {
            getHibernateTemplate().save(new UserSubscription(new UserSubscriptionPK(user.getId(), userToSubs.getId())));
        } catch (ConstraintViolationException e) {
            throw new Exception(Constants.USER_ALREADY_SUBSCRIBED);
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public void createTopicSubsription(User user, Long topicIdToSubs) throws Exception {
        try {
            getHibernateTemplate().save(new TopicSubscription(new TopicSubscriptionPK(user.getId(), topicIdToSubs)));
        } catch (ConstraintViolationException e) {
            throw new Exception(Constants.TOPIC_ALREADY_SUBSCRIBED);
        } catch (Exception e) {
            throw e;
        }
    }

    private void changeMaxResults(int numMax) {
        getHibernateTemplate().setMaxResults(numMax);
    }

    private void restoreMaxResults() {
        getHibernateTemplate().setMaxResults(Constants.MAX_RESULTS);
    }

    @Override
    public List<ReadingProfile> getAllReadingProfiles(int from, int nmrRcrds) {
        DetachedCriteria cri = DetachedCriteria.forClass(ReadingProfile.class);
        List<ReadingProfile> pflsLst = getHibernateTemplate().findByCriteria(cri, from, nmrRcrds);
        getHibernateTemplate().setMaxResults(100);
        return pflsLst;
    }

    @Override
    public void deleteCluster(ReadingProfile pfl) {
        getHibernateTemplate().delete(pfl);
    }

    @Override
    public List<UserClustersRecommendation> getClusters(User connectedUser, List<User> ngbsLst) throws Exception {

        String qryUsersIn;

        if (ngbsLst.size() > 0) {
            qryUsersIn = " in (";
            for (int i = 0; i < ngbsLst.size() - 1; i++) {
                qryUsersIn += ngbsLst.get(i).getId().toString() + ",";
            }

            qryUsersIn += ngbsLst.get(ngbsLst.size() - 1).getId().toString() + ") ";

        } else {
            qryUsersIn = " = uc.idUser ";
        }

        changeMaxResults(Constants.MAX_MATCHING_CLUSTERS);

        String query = "select uc "
                + "from UserClustersRecommendation uc "
                + "where uc.idUser " + qryUsersIn + ""
                + "order by uc.sumFreq desc, idUser";

        List<UserClustersRecommendation> topicsLst = (List<UserClustersRecommendation>) getHibernateTemplate().find(query);
        restoreMaxResults();
        return topicsLst;

    }

    @Override
    public List<UserClustersRecommendation> fillClusters(User connectedUser, List<User> ngbsLst, String[] recommendedTopics) throws Exception {

        String qryTopicsNotIn;

        if (recommendedTopics.length > 0) {
            qryTopicsNotIn = " not in ( ";
            for (int i = 0; i < recommendedTopics.length - 1; i++) {
                qryTopicsNotIn += recommendedTopics[i] + ",";
            }

            qryTopicsNotIn += recommendedTopics[recommendedTopics.length - 1] + ") ";

        } else {
            qryTopicsNotIn = " = uc.idConcept ";
        }

        String qryUsersIn;

        if (ngbsLst.size() > 0) {
            qryUsersIn = " in (";
            for (int i = 0; i < ngbsLst.size() - 1; i++) {
                qryUsersIn += ngbsLst.get(i).getId().toString() + ",";
            }

            qryUsersIn += ngbsLst.get(ngbsLst.size() - 1).getId().toString() + ") ";

        } else {
            qryUsersIn = " = uc.idUser ";
        }

        changeMaxResults(Constants.MAX_MATCHING_CLUSTERS);

        String query = "select uc "
                + "from UserClustersRecommendation uc "
                + "where uc.idUser " + qryUsersIn + ""
                + "and uc.idConcept " + qryTopicsNotIn + ""
                + "order by uc.sumFreq desc, idUser";

        logger.info("Matching clusters query: " + query);

        List<UserClustersRecommendation> topicsLst = (List<UserClustersRecommendation>) getHibernateTemplate().find(query);

        restoreMaxResults();

        return topicsLst;
    }

    @Override
    public Long getTopicByName(String topic) {
        List<Topic> topicLst = (List<Topic>) getHibernateTemplate().find("SELECT c FROM Topic c WHERE c.valor = ?", topic);
        if (topicLst.size() > 0) {
            return topicLst.get(0).getId();
        } else {
            return null;
        }
    }

    @Override
    public Long getClustersNumber() {
        return ((Long) getHibernateTemplate().find("select count(*) from ReadingProfile").get(0)).longValue();
    }

    @Override
    public List<Topic> getTopicLstLikeName(String topic) {

        String topicAux = "%" + topic.toLowerCase() + "%";
        List<Topic> topicLst = (List<Topic>) getHibernateTemplate().find("SELECT c FROM Topic c WHERE Lower(c.valor) like ?", topicAux);
        return topicLst;
    }
}
